package model.data.database;

import java.util.ArrayList;
import java.util.List;

import model.Category;
import model.data.CategoryStore;

public class CategoryTable implements CategoryStore {
	/* Handles requests to the database */
	DatabaseHandler dbHandler;
	
	/** Constructs a new category table */
	public CategoryTable() {
		this.dbHandler = new DatabaseHandler();
	}
	
	/** Writes a new account to the database */
	@Override
	public void write(Category category) throws Exception {
		this.dbHandler.callUpdateProcedure("sp_insertcategory(?)", category.getName());
	}

	/** Updates an existing account in the database */
	@Override
	public void update(Category category) {
		// TODO Auto-generated method stub
	}

	/** Deletes an existing account in the database */
	@Override
	public void delete(Category category) throws Exception {
		this.dbHandler.callUpdateProcedure("sp_deletecategory(?)", category.getId());
	}

	/** Returns the number of records in the table */
	@Override
	public int getCount() throws Exception {
		int count = 0;
		ArrayList<DataRecord> records;

		// Get records
		records = (ArrayList<DataRecord>)this.dbHandler.callQueryProcedure("sp_getcountcategory");
		
		// Fill list
		for (DataRecord dr : records) {
			count = Integer.parseInt(dr.getValue("Result"));
		}
		
		// Return result
		return count;
	}

	/** Returns a category by id */
	@Override
	public List<Category> getById(long id) throws Exception {
		ArrayList<Category> categories;
		ArrayList<DataRecord> records;
		
		// Get records
		records = (ArrayList<DataRecord>)this.dbHandler.callQueryProcedure("sp_getbyidcategory(?)", id);
		
		// Create list
		categories = new ArrayList<Category>();
		
		// Fill list
		for (DataRecord dr : records) {
			Category category = new Category();
			category.setId(Long.parseLong(dr.getValue("CategoryID")));
			category.setName(dr.getValue("Name"));
			categories.add(category);
		}
		
		// Return result
		return categories;
	}

	/** Returns a category by name */
	@Override
	public List<Category> getByName(String name) throws Exception {
		ArrayList<Category> categories;
		ArrayList<DataRecord> records;
		
		// Get records
		records = (ArrayList<DataRecord>)this.dbHandler.callQueryProcedure("sp_getbynamecategory(?)", name);
		
		// Create list
		categories = new ArrayList<Category>();
		
		// Fill list
		for (DataRecord dr : records) {
			Category category = new Category();
			category.setId(Long.parseLong(dr.getValue("CategoryID")));
			category.setName(dr.getValue("Name"));
			categories.add(category);
		}
		
		// Return result
		return categories;
	}
}